﻿using Cat.BaseModel;
using Cat.DataAccess;
using Cat.Util;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Cat.BussinessService.Dal
{
    internal class cb_projectDal
    {
        private static string _DatabaseName = "Cat_bank";
        internal static List<BaseModel.cb_project> GetUnion(string searchName, DateTime startTime, DateTime endTime, BaseModel.GridPagerParam param, long operationBy, out int total)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM cb_project where (cb_projectName like '%?searchName%' or (createTime BETWEEN ?startTime and ?endTime)) and createBy=?operationBy");
            strSql.Append(" order by createTime desc");
            strSql.Append(" limit ");
            strSql.Append(((param.CurrentPage - 1) * param.PageSize).ToString() + "," + param.PageSize.ToString());
            total = 0;
            var parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter("?searchName", searchName));
            parameters.Add(new MySqlParameter("?startTime", startTime)); parameters.Add(new MySqlParameter("?endTime", endTime)); parameters.Add(new MySqlParameter("?operationBy", operationBy));

            parameters.Add(new MySqlParameter("?CurrentPage", param.CurrentPage));

            parameters.Add(new MySqlParameter("?PageSize", param.PageSize));

            List<BaseModel.cb_project> resultList = new List<BaseModel.cb_project>();
            using (var dataReader = DatabaseFactory.GetDataReader(_DatabaseName, parameters, strSql.ToString(), CommandType.Text))
            {
                while (dataReader.Read())
                {
                    resultList.Add(new BaseModel.cb_project()
                    {
                        projectId = dataReader.GetInt32("projectId"),
                        projectName = dataReader["projectName"].ToString(),
                        productId = dataReader["productId"].ToString(),
                        grantAmount = dataReader.GetDecimal("grantAmount"),
                        thresholdId = dataReader.GetInt32("thresholdId"),
                        deadlineId = dataReader.GetInt32("deadlineId"),
                        returnModeId = dataReader.GetInt32("returnModeId"),
                        rateId = dataReader.GetInt32("rateId"),
                        earningsTime = dataReader.GetDateTime("earningsTime"),
                        accountTime = dataReader.GetDateTime("accountTime"),
                        paymentTime = dataReader.GetDateTime("paymentTime"),
                        overTime = dataReader.GetDateTime("overTime"),
                        ishot = dataReader.GetByte("ishot"),
                        iscopes = dataReader.GetByte("iscopes"),
                        isBuyOrStart = dataReader.GetByte("isBuyOrStart"),
                        buyOrStartInterest = dataReader.GetFloat("buyOrStartInterest"),
                        isState = dataReader.GetByte("isState"),
                        isShow = dataReader.GetByte("isShow"),
                        updatetime = dataReader.GetDateTime("updatetime"),
                        modifyBy = dataReader.GetInt32("modifyBy"),
                        createTime = dataReader.GetDateTime("createTime"),
                        createBy = dataReader.GetInt32("createBy"),
                    });
                }
            } return resultList;
        }
        internal static List<BaseModel.cb_project> GetJoin(string searchName, DateTime startTime, DateTime endTime, BaseModel.GridPagerParam param, long operationBy, out int total)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM cb_project where cb_projectName like CONCAT('%',?searchName,'%') and (createTime BETWEEN ?startTime AND ?endTime) and createBy=?operationBy");
            strSql.Append(" order by createTime desc");
            strSql.Append(" limit ");
            strSql.Append(((param.CurrentPage - 1) * param.PageSize).ToString() + "," + param.PageSize.ToString());
            total = 0;
            var parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter("?searchName", searchName));
            parameters.Add(new MySqlParameter("?startTime", startTime));
            parameters.Add(new MySqlParameter("?endTime", endTime));
            parameters.Add(new MySqlParameter("?operationBy", operationBy));
            parameters.Add(new MySqlParameter("?CurrentPage", param.CurrentPage));
            parameters.Add(new MySqlParameter("?PageSize", param.PageSize));
            List<BaseModel.cb_project> resultList = new List<BaseModel.cb_project>();
            using (var dataReader = DatabaseFactory.GetDataReader(_DatabaseName, parameters, strSql.ToString(), CommandType.Text))
            {
                while (dataReader.Read())
                {
                    resultList.Add(new BaseModel.cb_project()
                    {
                        projectId = dataReader.GetInt32("projectId"),
                        projectName = dataReader["projectName"].ToString(),
                        productId = dataReader["productId"].ToString(),
                        grantAmount = dataReader.GetDecimal("grantAmount"),
                        thresholdId = dataReader.GetInt32("thresholdId"),
                        deadlineId = dataReader.GetInt32("deadlineId"),
                        returnModeId = dataReader.GetInt32("returnModeId"),
                        rateId = dataReader.GetInt32("rateId"),
                        earningsTime = dataReader.GetDateTime("earningsTime"),
                        accountTime = dataReader.GetDateTime("accountTime"),
                        paymentTime = dataReader.GetDateTime("paymentTime"),
                        overTime = dataReader.GetDateTime("overTime"),
                        ishot = dataReader.GetByte("ishot"),
                        iscopes = dataReader.GetByte("iscopes"),
                        isBuyOrStart = dataReader.GetByte("isBuyOrStart"),
                        buyOrStartInterest = dataReader.GetFloat("buyOrStartInterest"),
                        isState = dataReader.GetByte("isState"),
                        isShow = dataReader.GetByte("isShow"),
                        updatetime = dataReader.GetDateTime("updatetime"),
                        modifyBy = dataReader.GetInt32("modifyBy"),
                        createTime = dataReader.GetDateTime("createTime"),
                        createBy = dataReader.GetInt32("createBy"),
                    });
                }
            } return resultList;
        }
        internal static BaseModel.cb_project GetDetail(int operationBy, int cb_projectId)
        {
            cb_project r = null;
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter("?operationBy", operationBy));
            using (var dataReader = DatabaseFactory.GetDataReader(_DatabaseName, parameters, "select * from cb_member where cb_projectId=?cb_projectId", CommandType.Text))
            {
                if (dataReader.Read())
                {
                    r = new cb_project();
                    r.projectId = dataReader.GetInt32("projectId"); r.projectName = dataReader["projectName"].ToString(); r.productId = dataReader["productId"].ToString(); r.grantAmount = dataReader.GetDecimal("grantAmount"); r.thresholdId = dataReader.GetInt32("thresholdId"); r.deadlineId = dataReader.GetInt32("deadlineId"); r.returnModeId = dataReader.GetInt32("returnModeId"); r.rateId = dataReader.GetInt32("rateId"); r.earningsTime = dataReader.GetDateTime("earningsTime"); r.accountTime = dataReader.GetDateTime("accountTime"); r.paymentTime = dataReader.GetDateTime("paymentTime"); r.overTime = dataReader.GetDateTime("overTime"); r.ishot = dataReader.GetByte("ishot"); r.iscopes = dataReader.GetByte("iscopes"); r.isBuyOrStart = dataReader.GetByte("isBuyOrStart"); r.buyOrStartInterest = dataReader.GetFloat("buyOrStartInterest"); r.isState = dataReader.GetByte("isState"); r.isShow = dataReader.GetByte("isShow"); r.updatetime = dataReader.GetDateTime("updatetime"); r.modifyBy = dataReader.GetInt32("modifyBy"); r.createTime = dataReader.GetDateTime("createTime"); r.createBy = dataReader.GetInt32("createBy");
                }
            } return r;
        }
        internal static int Create(BaseModel.cb_project model)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter("?projectId", model.projectId));
            parameters.Add(new MySqlParameter("?projectName", model.projectName));
            parameters.Add(new MySqlParameter("?productId", model.productId));
            parameters.Add(new MySqlParameter("?grantAmount", model.grantAmount));
            parameters.Add(new MySqlParameter("?thresholdId", model.thresholdId));
            parameters.Add(new MySqlParameter("?deadlineId", model.deadlineId));
            parameters.Add(new MySqlParameter("?returnModeId", model.returnModeId));
            parameters.Add(new MySqlParameter("?rateId", model.rateId));
            parameters.Add(new MySqlParameter("?earningsTime", model.earningsTime));
            parameters.Add(new MySqlParameter("?accountTime", model.accountTime));
            parameters.Add(new MySqlParameter("?paymentTime", model.paymentTime));
            parameters.Add(new MySqlParameter("?overTime", model.overTime));
            parameters.Add(new MySqlParameter("?ishot", model.ishot));
            parameters.Add(new MySqlParameter("?iscopes", model.iscopes));
            parameters.Add(new MySqlParameter("?isBuyOrStart", model.isBuyOrStart));
            parameters.Add(new MySqlParameter("?buyOrStartInterest", model.buyOrStartInterest));
            parameters.Add(new MySqlParameter("?isState", model.isState));
            parameters.Add(new MySqlParameter("?isShow", model.isShow));
            parameters.Add(new MySqlParameter("?updatetime", model.updatetime));
            parameters.Add(new MySqlParameter("?modifyBy", model.modifyBy));
            parameters.Add(new MySqlParameter("?createTime", model.createTime));
            parameters.Add(new MySqlParameter("?createBy", model.createBy));

            ; return DatabaseFactory.GetPrimarykey(_DatabaseName, parameters.ToArray(), "insert into cb_project(projectId,projectName,productId,grantAmount,thresholdId,deadlineId,returnModeId,rateId,earningsTime,accountTime,paymentTime,overTime,ishot,iscopes,isBuyOrStart,buyOrStartInterest,isState,isShow,updatetime,modifyBy,createTime,createBy)  values (?projectId,?projectName,?productId,?grantAmount,?thresholdId,?deadlineId,?returnModeId,?rateId,?earningsTime,?accountTime,?paymentTime,?overTime,?ishot,?iscopes,?isBuyOrStart,?buyOrStartInterest,?isState,?isShow,?updatetime,?modifyBy,?createTime,?createBy);SELECT  @@IDENTITY as id", CommandType.Text);
        }
        internal static bool Modify(BaseModel.cb_project model)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();
            parameters.Add(new MySqlParameter("?projectId", model.projectId));
            parameters.Add(new MySqlParameter("?projectName", model.projectName));
            parameters.Add(new MySqlParameter("?productId", model.productId));
            parameters.Add(new MySqlParameter("?grantAmount", model.grantAmount));
            parameters.Add(new MySqlParameter("?thresholdId", model.thresholdId));
            parameters.Add(new MySqlParameter("?deadlineId", model.deadlineId));
            parameters.Add(new MySqlParameter("?returnModeId", model.returnModeId));
            parameters.Add(new MySqlParameter("?rateId", model.rateId));
            parameters.Add(new MySqlParameter("?earningsTime", model.earningsTime));
            parameters.Add(new MySqlParameter("?accountTime", model.accountTime));
            parameters.Add(new MySqlParameter("?paymentTime", model.paymentTime));
            parameters.Add(new MySqlParameter("?overTime", model.overTime));
            parameters.Add(new MySqlParameter("?ishot", model.ishot));
            parameters.Add(new MySqlParameter("?iscopes", model.iscopes));
            parameters.Add(new MySqlParameter("?isBuyOrStart", model.isBuyOrStart));
            parameters.Add(new MySqlParameter("?buyOrStartInterest", model.buyOrStartInterest));
            parameters.Add(new MySqlParameter("?isState", model.isState));
            parameters.Add(new MySqlParameter("?isShow", model.isShow));
            parameters.Add(new MySqlParameter("?updatetime", model.updatetime));
            parameters.Add(new MySqlParameter("?modifyBy", model.modifyBy));
            parameters.Add(new MySqlParameter("?createTime", model.createTime));
            parameters.Add(new MySqlParameter("?createBy", model.createBy));

            return DatabaseFactory.ExecuteNonQuery(_DatabaseName, parameters, "update cb_project set ?projectId=model.projectId,?projectName=model.projectName,?productId=model.productId,?grantAmount=model.grantAmount,?thresholdId=model.thresholdId,?deadlineId=model.deadlineId,?returnModeId=model.returnModeId,?rateId=model.rateId,?earningsTime=model.earningsTime,?accountTime=model.accountTime,?paymentTime=model.paymentTime,?overTime=model.overTime,?ishot=model.ishot,?iscopes=model.iscopes,?isBuyOrStart=model.isBuyOrStart,?buyOrStartInterest=model.buyOrStartInterest,?isState=model.isState,?isShow=model.isShow,?updatetime=model.updatetime,?modifyBy=model.modifyBy,?createTime=model.createTime,?createBy=model.createBy where userID=?userID;", CommandType.Text) > 0 ? true : false;
        }
    }
}